package com.example.project3.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.example.project3.model.Book;
import java.util.ArrayList;
import java.util.List;

/**
 * 书籍数据库帮助类
 * 管理书籍数据的增删改查操作
 */
public class BookDatabaseHelper extends SQLiteOpenHelper {
    
    // 数据库信息
    private static final String DATABASE_NAME = "book_management.db";
    private static final int DATABASE_VERSION = 1;
    
    // 表名和字段
    private static final String TABLE_BOOKS = "books";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_BOOK_NUMBER = "book_number";
    private static final String COLUMN_BOOK_NAME = "book_name";
    private static final String COLUMN_AUTHOR = "author";
    private static final String COLUMN_PRICE = "price";
    private static final String COLUMN_USER_EMAIL = "user_email";
    
    // 创建表的SQL语句
    private static final String CREATE_TABLE_BOOKS = 
            "CREATE TABLE " + TABLE_BOOKS + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_BOOK_NUMBER + " TEXT NOT NULL, " +
            COLUMN_BOOK_NAME + " TEXT NOT NULL, " +
            COLUMN_AUTHOR + " TEXT, " +
            COLUMN_PRICE + " REAL, " +
            COLUMN_USER_EMAIL + " TEXT NOT NULL" +
            ")";

    public BookDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_BOOKS);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOKS);
        onCreate(db);
    }

    /**
     * 添加书籍
     */
    public long addBook(Book book) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        
        values.put(COLUMN_BOOK_NUMBER, book.getBookNumber());
        values.put(COLUMN_BOOK_NAME, book.getBookName());
        values.put(COLUMN_AUTHOR, book.getAuthor());
        values.put(COLUMN_PRICE, book.getPrice());
        values.put(COLUMN_USER_EMAIL, book.getUserEmail());
        
        long result = db.insert(TABLE_BOOKS, null, values);
        db.close();
        return result;
    }

    /**
     * 获取指定用户的所有书籍
     */
    public List<Book> getAllBooks(String userEmail) {
        List<Book> bookList = new ArrayList<>();
        String selectQuery = "SELECT * FROM " + TABLE_BOOKS + 
                            " WHERE " + COLUMN_USER_EMAIL + " = ? " +
                            " ORDER BY " + COLUMN_ID + " DESC";
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, new String[]{userEmail});
        
        if (cursor.moveToFirst()) {
            do {
                Book book = new Book();
                book.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                book.setBookNumber(cursor.getString(cursor.getColumnIndex(COLUMN_BOOK_NUMBER)));
                book.setBookName(cursor.getString(cursor.getColumnIndex(COLUMN_BOOK_NAME)));
                book.setAuthor(cursor.getString(cursor.getColumnIndex(COLUMN_AUTHOR)));
                book.setPrice(cursor.getDouble(cursor.getColumnIndex(COLUMN_PRICE)));
                book.setUserEmail(cursor.getString(cursor.getColumnIndex(COLUMN_USER_EMAIL)));
                
                bookList.add(book);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        db.close();
        return bookList;
    }

    /**
     * 根据书号或书名查询书籍
     */
    public List<Book> searchBooks(String userEmail, String bookNumber, String bookName) {
        List<Book> bookList = new ArrayList<>();
        StringBuilder queryBuilder = new StringBuilder();
        List<String> selectionArgs = new ArrayList<>();
        
        queryBuilder.append("SELECT * FROM ").append(TABLE_BOOKS);
        queryBuilder.append(" WHERE ").append(COLUMN_USER_EMAIL).append(" = ?");
        selectionArgs.add(userEmail);
        
        // 如果书号不为空，添加书号查询条件
        if (bookNumber != null && !bookNumber.trim().isEmpty()) {
            queryBuilder.append(" AND ").append(COLUMN_BOOK_NUMBER).append(" LIKE ?");
            selectionArgs.add("%" + bookNumber.trim() + "%");
        }
        
        // 如果书名不为空，添加书名查询条件
        if (bookName != null && !bookName.trim().isEmpty()) {
            queryBuilder.append(" AND ").append(COLUMN_BOOK_NAME).append(" LIKE ?");
            selectionArgs.add("%" + bookName.trim() + "%");
        }
        
        queryBuilder.append(" ORDER BY ").append(COLUMN_ID).append(" DESC");
        
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(queryBuilder.toString(), 
                                   selectionArgs.toArray(new String[0]));
        
        if (cursor.moveToFirst()) {
            do {
                Book book = new Book();
                book.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                book.setBookNumber(cursor.getString(cursor.getColumnIndex(COLUMN_BOOK_NUMBER)));
                book.setBookName(cursor.getString(cursor.getColumnIndex(COLUMN_BOOK_NAME)));
                book.setAuthor(cursor.getString(cursor.getColumnIndex(COLUMN_AUTHOR)));
                book.setPrice(cursor.getDouble(cursor.getColumnIndex(COLUMN_PRICE)));
                book.setUserEmail(cursor.getString(cursor.getColumnIndex(COLUMN_USER_EMAIL)));
                
                bookList.add(book);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        db.close();
        return bookList;
    }

    /**
     * 更新书籍信息
     */
    public int updateBook(Book book) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        
        values.put(COLUMN_BOOK_NUMBER, book.getBookNumber());
        values.put(COLUMN_BOOK_NAME, book.getBookName());
        values.put(COLUMN_AUTHOR, book.getAuthor());
        values.put(COLUMN_PRICE, book.getPrice());
        
        int result = db.update(TABLE_BOOKS, values, 
                              COLUMN_ID + " = ? AND " + COLUMN_USER_EMAIL + " = ?",
                              new String[]{String.valueOf(book.getId()), book.getUserEmail()});
        db.close();
        return result;
    }

    /**
     * 删除书籍
     */
    public void deleteBook(int bookId, String userEmail) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_BOOKS, 
                 COLUMN_ID + " = ? AND " + COLUMN_USER_EMAIL + " = ?",
                 new String[]{String.valueOf(bookId), userEmail});
        db.close();
    }

    /**
     * 根据ID获取书籍
     */
    public Book getBook(int bookId, String userEmail) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_BOOKS,
                new String[]{COLUMN_ID, COLUMN_BOOK_NUMBER, COLUMN_BOOK_NAME, 
                            COLUMN_AUTHOR, COLUMN_PRICE, COLUMN_USER_EMAIL},
                COLUMN_ID + " = ? AND " + COLUMN_USER_EMAIL + " = ?",
                new String[]{String.valueOf(bookId), userEmail},
                null, null, null);
        
        Book book = null;
        if (cursor != null && cursor.moveToFirst()) {
            book = new Book();
            book.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
            book.setBookNumber(cursor.getString(cursor.getColumnIndex(COLUMN_BOOK_NUMBER)));
            book.setBookName(cursor.getString(cursor.getColumnIndex(COLUMN_BOOK_NAME)));
            book.setAuthor(cursor.getString(cursor.getColumnIndex(COLUMN_AUTHOR)));
            book.setPrice(cursor.getDouble(cursor.getColumnIndex(COLUMN_PRICE)));
            book.setUserEmail(cursor.getString(cursor.getColumnIndex(COLUMN_USER_EMAIL)));
            cursor.close();
        }
        
        db.close();
        return book;
    }
}